package managers;

import java.sql.SQLException;
import java.util.Iterator;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

import util.Codes;

import business.Profile;
import business.Program;
import util.Table;

public class MngProfiles extends Manager {
	
	public MngProfiles() {
		
	}
	
	public Profile getProfile(int prfID) {
		Profile prf = new Profile();
		prf.setId(prfID);
		
		String sql = "SELECT prf.profile, prg.id " +
					 "FROM profiles prf " +
					 "INNER JOIN access acc ON prf.id = acc.profile " +
					 "INNER JOIN programs prg ON  acc.program = prg.id " +
					 "WHERE prf.id = " + prfID;
		
		try {
			ArrayList<Program> programs = new ArrayList<Program>();
			rst = stmt.executeQuery(sql);
			int n = 0;
			while (rst.next()) {
				if (n == 0) {
					prf.setProfile(rst.getString("profile"));
				}
				Program prg = new Program();
				prg.setId(rst.getByte("id"));
				programs.add(prg);
				
				n ++;
			}
			prf.setPrograms(programs);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return prf;
	}

    public ArrayList<Profile> getProfiles() {
        return getAllProfiles(false);
    }

    public ArrayList<Profile> getAllProfiles() {
        return getAllProfiles(true);
    }

	private ArrayList<Profile> getAllProfiles(boolean prg) {
		ArrayList<Profile> profiles = new ArrayList<Profile>();
		
		try {
			String sql = "SELECT id, profile FROM profiles";
			rst = stmt.executeQuery(sql);

            MngPrograms mngPrograms = new MngPrograms();
			while (rst.next()) {
				Profile prf = new Profile();
				prf.setId(rst.getInt("id"));
				prf.setProfile(rst.getString("profile"));

                if (prg) prf.setPrograms(mngPrograms.getProgramsByProfile(prf.getId()));

                profiles.add(prf);
			}
			
		}	catch(SQLException e) {
			e.printStackTrace();
		}
		
		return profiles;
	}
	
	public boolean saveProfile(int prfID, String profile, String lstPrgs) {
		Profile prf = new Profile();
		prf.setId(prfID);
		prf.setProfile(profile);
		
		ArrayList<Program> programs = new ArrayList<Program>();
		
		String arrPrgs[] = lstPrgs.split(",");
		int na = arrPrgs.length, p;
		
		for (p = 0; p < na; p ++) {
			Program prg = new Program();
			prg.setId(Byte.parseByte(arrPrgs[p]));
			programs.add(prg);
		}
		
		prf.setPrograms(programs);
		
		return saveProfile(prf);
	}
	
	public boolean saveProfile(Profile prf) {
		boolean res = false;
		String sql;
		
		if (prf.getId() > 0) {
			sql = "UPDATE profiles SET profile = '" + prf.getProfile() + "' WHERE id = " + prf.getId();
		}	else {
			sql = "INSERT INTO profiles (profile) VALUES ('" + prf.getProfile() + "')";
		}
		try {
			if (stmt.executeUpdate(sql) > 0) {
				rst = stmt.getGeneratedKeys();
				if (rst.next()) prf.setId(rst.getInt(1));
				
				String sqlDel = "DELETE access.* FROM access WHERE profile = " + prf.getId();
				
				stmt.executeUpdate(sqlDel);
				
				ArrayList<Program> prgs = prf.getPrograms();
				Iterator<Program> itePrg = prgs.iterator();
				
				String sqlIns = "INSERT INTO access (profile, program) VALUES";
				while (itePrg.hasNext()) {
					sqlIns += " (" + prf.getId() + ", " + itePrg.next().getId() + "), ";
				}
				sqlIns = sqlIns.substring(0, sqlIns.length() - 2);
				
				if (stmt.executeUpdate(sqlIns) > 0) res = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return res;
	}
	
	public boolean deleteProfile(int prfID) {
		boolean res = false;
		
		String sqlAcc = "DELETE access.* FROM access WHERE profile = " + prfID;
		String sql = "DELETE profiles.* FROM profiles WHERE id = " + prfID;
		try {
			if (stmt.executeUpdate(sqlAcc) > 0) {
				if (stmt.executeUpdate(sql) > 0) res = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return res;
	}
	
	public String getTable() {
		String sql;

		sql = "SELECT * FROM profiles";

        return Table.getTable(sql, "profile", "editProfile", "id", "delProfile", "id");
	}
}